-- Indexes to speed up EPSS inserter
CREATE INDEX IF NOT EXISTS vulnerability_equivalent_b_idx ON vulnerability.equivalent (b);

CREATE INDEX IF NOT EXISTS vulnerability_vulnerability_source_id_idx ON vulnerability.vulnerability (source_id);

-- Table to hold the CISA Known Exploited vulnerabilities
CREATE TABLE IF NOT EXISTS vulnerability.cisa_known_exploited (
    "id" uuid NOT NULL DEFAULT gen_random_uuid(),
    cve TEXT UNIQUE,
    vendor_project text NOT NULL,
    product text NOT NULL,
    vulnerability_name text NOT NULL,
    date_added date NOT NULL,
    short_description text NOT NULL,
    required_action text NOT NULL,
    due_date date NOT NULL,
    notes text NOT NULL,
    PRIMARY KEY ("id"),
    CHECK (cve LIKE 'CVE-%')
);

CREATE OR REPLACE FUNCTION vulnerability.cisa_known_exploited_vulnerability(known_exploited vulnerability.cisa_known_exploited)
    RETURNS SETOF vulnerability.vulnerability AS $$
    SELECT *
    FROM vulnerability.vulnerability
    WHERE source_id = known_exploited.cve
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION vulnerability.vulnerability_cisa_known_exploited(vulnerability vulnerability.vulnerability)
 RETURNS SETOF vulnerability.cisa_known_exploited
 LANGUAGE sql
 STABLE
AS $function$
    SELECT *
    FROM vulnerability.cisa_known_exploited
    WHERE cve = vulnerability.source_id
$function$
